package ca.cbc.giscommon.dao;

import ca.cbc.giscommon.entity.Source;
import ca.cbc.giscommon.entity.Tweet;
import ca.cbc.giscommon.entity.Zone;
import ca.cbc.giscommon.util.GeometryUtil;
import ca.cbc.giscommon.util.StringUtil;
import com.vividsolutions.jts.geom.Geometry;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Formatter;
import java.util.List;

public class TweetDAO extends EntityHome<Tweet> {

    @Override
    public String getFullClassName() {
        return Tweet.class.getCanonicalName();
    }

    public void delete(Source source) {
        getCurrentSession().createQuery("DELETE Tweet t WHERE t.source = :source")
                .setEntity("source", source)
                .executeUpdate();
    }

    public List<Tweet> listWithZone() {
        return this.listWithZone(null, null, null, -1l, -1l, "createdat", false, 50, 0);
    }

    @SuppressWarnings("unchecked")
    public List<Tweet> listWithZone(String zoneIds, Geometry bbox, String categoryIds, long startTime, long endTime,
                                    String orderBy, boolean isAscending, int limit, int offset) {
        StringBuilder queryBuilder = null;
        if (!StringUtil.instance.isNullOrEmpty(categoryIds)) {
            queryBuilder = new StringBuilder("SELECT {t.*}, {z.*} FROM zone z, twitter_tweets t, category c, source so WHERE so.category_id = c.id and ST_Intersects(z.the_geom, t.the_geom)");
            queryBuilder.append(" and t.source_id = so.id and c.id in (").append(categoryIds).append(")");
        } else {
            queryBuilder = new StringBuilder("SELECT {t.*}, {z.*} FROM zone z, twitter_tweets t WHERE ST_Intersects(z.the_geom, t.the_geom)");
        }

        if (!StringUtil.instance.isNullOrEmpty(zoneIds)) {
            queryBuilder.append(" and z.id in (").append(zoneIds).append(")");
        } else if (bbox != null) {
            queryBuilder.append(new Formatter().format(" and ST_Intersects(ST_GeomFromText('%s', %d), t.the_geom)", bbox.toString(), GeometryUtil.instance.getSRID()).toString());
        }

        if (startTime > 0l) {
            queryBuilder.append(" and createdat >= '").append(new Timestamp(startTime)).append("'");
        }

        if (endTime > 0l) {
            queryBuilder.append(" and createdat <= '").append(new Timestamp(endTime)).append("'");
        }

        queryBuilder.append(new Formatter().format(" ORDER BY t.%s %s", orderBy, getAscOrDesc(isAscending)));

        if (limit > 0) {
            queryBuilder.append(" LIMIT " + limit);
        }

        if (offset > 0) {
            queryBuilder.append(" OFFSET " + offset);
        }

        List<Object> tweetsWithZones = getCurrentSession().createSQLQuery(queryBuilder.toString()).addEntity("t", Tweet.class).addEntity("z", Zone.class).list();
        List<Tweet> tweets = new ArrayList<Tweet>();
        for (Object obj : tweetsWithZones) {
            Object[] objArray = (Object[]) obj;
            if (objArray.length != 2) {
                logger.error("Error! Something is wrong when fetch tweets {}.", Arrays.toString(objArray));
                continue;
            }

            Tweet tweet = (Tweet) objArray[0];
            Zone zone = (Zone) objArray[1];
            tweet.setZone(zone);
            tweets.add(tweet);
        }
        logger.debug("Successfully retrieved {} records.", tweets.size());
        return tweets;
    }
}
